import akshare as ak
import pandas as pd
import numpy as np
from datetime import datetime
from sqlalchemy import create_engine, distinct, or_, and_
import backtrader as bt
import pymssql
from urllib.parse import quote_plus as urlquote
from configparser import ConfigParser
import time

conf = ConfigParser()
conf.read('env.ini')

sqlserver = ('sqlserver_out', 'sqlserver_in')[0]

host = conf.get(sqlserver, 'host')
port = conf.get(sqlserver, 'port')
user = conf.get(sqlserver, 'user')
password = conf.get(sqlserver, 'password')
schema = conf.get(sqlserver, 'schema')
db_name = conf.get(sqlserver, 'db')


def get_stocks():
    df = ak.stock_sh_a_spot_em()
    ret = df[~df['名称'].str.contains('退市')][['代码', '名称', '最新价', '总市值', '流通市值', '换手率']]
    df = ak.stock_sz_a_spot_em()
    ret2 = df[~df['名称'].str.contains('退市')][['代码', '名称', '最新价', '总市值', '流通市值', '换手率']]
    ret = ret.append(ret2, ignore_index=True)
    stocks = list(set(ret['代码'].to_list()))

    return stocks


def get_instocks():
    sql = f"SELECT DISTINCT code FROM HIS_DAILY_DATA"
    r = exec_sql(sql)
    stocks = set([tok[0] for tok in r])

    return stocks


def insert_data():
    stocks = get_stocks()
    nt = datetime.strftime(datetime.now(), '%Y%m%d')
    table_name = 'HIS_DAILY_DATA'

    for i, code in enumerate(stocks):
        df = ak.stock_zh_a_hist(symbol=code, period="daily", start_date="20170101",
                                end_date=nt, adjust="qfq")
        df.rename(columns={'日期': 'datetime', '开盘': 'open', '收盘': 'close', '最高': 'high', '最低': 'low',
                           '成交量': 'vol', '成交额': 'amount', '振幅': 'zf', '涨跌幅': 'zdf', '涨跌额': 'zde',
                           '换手率': 'hsl'}, inplace=True)
        df['datetime'] = df['datetime'].map(time_map)
        df.insert(0, 'code', code)
        insert_db(df, table_name)
        print(f'i={i}->len={len(stocks)}->{code} insert ok!!')
        time.sleep(3)


def time_map(x):
    t = int(datetime.strftime(x, '%Y%m%d'))
    return t


def insert_db(df, table_name):
    yconnect = create_engine(f'mssql+pymssql://{user}:{urlquote(password)}@{host}:{port}/{db_name}?charset=utf8')
    pd.io.sql.to_sql(df, table_name, yconnect, schema=schema, if_exists='append', index=False)


def exec_sql(sql):
    conn = pymssql.connect(host=host, port=port, user=user, password=password, database=db_name)
    cursor = conn.cursor()
    cursor.execute(sql)
    r = cursor.fetchall()
    cursor.close()
    conn.close()
    return r


if __name__ == '__main__':
    instocks = get_instocks()
    print(instocks)
